#!/bin/bash
# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
# todo 大数据-电商数仓-07-商品主题商品诊断看板
if [ -n "$1" ] ; then
data_date=$1
else
data_date=`date -d "-1 days" +%F`
fi
# 加载数据语句
DIM_MD_AREAS_SQL="
USE product_order;
WITH t1 AS
(SELECT
        v.product_id,
        v.product_name,
        v.category_id,
        v.category_name,
        v.total_visits,
        v.search_visits,
        v.jhs_visits,
        v.direct_visits,
        c.payment_rate,
        c.addcart_rate,
        c.avg_customer_value,
        c.collect_rate,
        cnt.content_visits,
        cnt.content_collect_rate,
        cnt.content_addcart_rate,
        cnt.content_payment,
        cnt.content_buyers,
        n.new_payment_ratio,
        n.new_buyer_ratio,
        s.image_reviews,
        s.positive_reviews,
        s.refund_rate,
         ROUND(
                 (LEAST(v.search_visits, 100) * 0.10) +
                 (LEAST(v.jhs_visits, 100) * 0.05) +
                 (LEAST(v.direct_visits, 100) * 0.05) +
                 (LEAST(c.payment_rate, 100) * 0.12) +
                 (LEAST(c.addcart_rate, 100) * 0.09) +
                 (LEAST(c.avg_customer_value, 100) * 0.06) +
                 (LEAST(c.collect_rate, 100) * 0.03) +
                 (LEAST(cnt.content_visits, 100) * 0.03) +
                 (LEAST(cnt.content_collect_rate, 100) * 0.02) +
                 (LEAST(cnt.content_addcart_rate, 100) * 0.02) +
                 (LEAST(n.new_payment_ratio, 100) * 0.05) +
                 (LEAST(n.new_buyer_ratio, 100) * 0.05) +
                 (LEAST(s.image_reviews, 100) * 0.06) +
                 (LEAST(s.positive_reviews, 100) * 0.06) -
                 (LEAST(s.refund_rate, 100) * 0.03)
         , 2) AS total_score,
     c.dt  AS dt
FROM product_order.dws_product_traffic_metrics v
LEFT JOIN product_order.dws_product_conversion_metrics c ON v.product_id = c.product_id
LEFT JOIN product_order.dws_product_content_metrics cnt ON v.product_id = cnt.product_id
LEFT JOIN product_order.dws_product_newbuyer_metrics n ON v.product_id = n.product_id
LEFT JOIN product_order.dws_product_service_metrics s ON v.product_id = s.product_id
WHERE v.dt = '${data_date}')
INSERT OVERWRITE TABLE product_order.ads_product_comprehensive_metrics
SELECT *,
       CASE
           WHEN total_score >= 85 THEN 'A'
           WHEN total_score >= 70 THEN 'B'
           WHEN total_score >= 50 THEN 'C'
           ELSE 'D'
           END AS compete_level
FROM t1;
"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"